package com.situ.storehouse.dao;

import java.util.List;

import com.situ.storehouse.util.Dbutil;
import com.situ.storehouse.vo.CountVO;

public class CountDao {

	public List<CountVO> countSelect(Integer page, Integer limit, String storedate,String[] arr) {
		String sql=null;
		if(storedate==null || storedate.equals("")) {
			sql="(SELECT wares.wares_name,bill.`status`,SUM(bill.num) as num"
					+ " from wares inner join bill on wares.id=bill.wares_id"
					+ " where status=0"
					+ " group by wares.wares_name)"
					+ " UNION "
					+ " (SELECT wares.wares_name,bill.`status`,SUM(bill.num) as num"
					+ " from wares inner join bill on wares.id=bill.wares_id"
					+ " where status=1"
					+ " group by wares.wares_name) limit ?,?";
		}else {
			if(arr[0].length()==4) {//按照年查询
				sql="(SELECT wares.wares_name,bill.`status`,SUM(bill.num) as num"
						+ " from wares inner join bill on wares.id=bill.wares_id"
						+ " where (storedate between '"+arr[0]+"-01-01' and '"+arr[1]+"-01-01') and status=0"
						+ " group by wares.wares_name)"
						+ " UNION "
						+ " (SELECT wares.wares_name,bill.`status`,SUM(bill.num) as num"
						+ " from wares inner join bill on wares.id=bill.wares_id"
						+ " where (storedate between '"+arr[0]+"-01-01' and '"+arr[1]+"-01-01') and status=1"
						+ " group by wares.wares_name) limit ?,?";
			}else if(arr[0].length()==7) {//按照月查询
				sql="(SELECT wares.wares_name,bill.`status`,SUM(bill.num) as num"
						+ " from wares inner join bill on wares.id=bill.wares_id"
						+ " where (storedate between '"+arr[0]+"-01' and '"+arr[1]+"-01') and status=0"
						+ " group by wares.wares_name)"
						+ " UNION "
						+ " (SELECT wares.wares_name,bill.`status`,SUM(bill.num) as num"
						+ " from wares inner join bill on wares.id=bill.wares_id"
						+ " where (storedate between '"+arr[0]+"-01' and '"+arr[1]+"-01') and status=1"
						+ " group by wares.wares_name) limit ?,?";
			}else {//按照查询
				sql="(SELECT wares.wares_name,bill.`status`,SUM(bill.num) as num"
						+ " from wares inner join bill on wares.id=bill.wares_id"
						+ " where (storedate between '"+arr[0]+"' and '"+arr[1]+"') and status=0"
						+ " group by wares.wares_name)"
						+ " UNION "
						+ " (SELECT wares.wares_name,bill.`status`,SUM(bill.num) as num"
						+ " from wares inner join bill on wares.id=bill.wares_id"
						+ " where (storedate between '"+arr[0]+"' and '"+arr[1]+"') and status=1"
						+ " group by wares.wares_name) limit ?,?";
			}
		}
		return Dbutil.executeDQL(sql, CountVO.class,page,limit);
	
	}
}	
